<?php
/*******************************************************************************
Rd/Dao/Reserve/Status.php
Implements Reserve Status DAO for RD

Created by Troy Hurteau, NCSU Libraries, NC State University (libraries.opensource@ncsu.edu).

This file is part of NCSU's distribution of ReservesDirect. This version has not been downloaded from Emory University
or the original developers of ReservesDirect. Neither Emory University nor the original developers of ReservesDirect have authorized
or otherwise endorsed or approved this distribution of the software.

Copyright (c) 2004-2006 Emory University, Atlanta, Georgia.

Licensed under the NCSU ReservesDirect License, Version 2.0 (the "License"); 
you may not use this file except in compliance with the License. You may obtain a copy of the full License at
 http://www.lib.ncsu.edu/it/opensource/

ReservesDirect is distributed in the hope that it will be useful,
but is distributed "AS IS" and WITHOUT ANY WARRANTY, without even the
implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
PURPOSE, and without any warranty as to non-infringement of any third
party's rights. See the License for the specific language governing permissions and limitations under the License.

The original version of ReservesDirect is located at:
http://www.reservesdirect.org/

This version of ReservesDirect, distributed by NCSU, is located at:
http://code.google.com/p/reservesdirect-ncsu/

*******************************************************************************/

class Rd_Dao_Reserve_Status extends Rd_Dao{
	
	
	public static function get($id)
	{
		$id = Rd_Pdo::escapeInt($id);
		$sql = "SELECT * FROM reserve_status WHERE id = {$id};";
		$result = Rd_Pdo::all(Rd_Pdo::query($sql));
		if (0 == count($result)) {
			throw new Exception('Unable to find the requested reserve status in the database.');
		} else {
			return $result[0];
		}
	}
	
	public static function lookupEnabled()
	{
		$labelField = Account_Rd::atLeastStaff() ? 'staff_label' : 'faculty_label';
		$sql = "SELECT id FROM reserve_status WHERE `enabled` ORDER BY `order`, {$labelField};";
		$result = Rd_Pdo::all(Rd_Pdo::query($sql));
		return $result;
	}
	
	public static function lookupByName($staffLabel) //#TODO probablt deprecate, unreliable
	{
		$cleanStaffLabel = Rd_Pdo::escapeString($staffLabel);
		$sql = "SELECT id FROM reserve_status WHERE LOWER(`staff_label`) = LOWER({$cleanStaffLabel}) LIMIT 1;";
		$result = Rd_Pdo::one(Rd_Pdo::query($sql));
		if ($result && array_key_exists('id', $result)) {
			return $result['id'];
		} else {
			throw new Exception("Missing status {$staffLabel} in the database.");
		}
	}
	
	public static function lookupByToken($token)
	{
		$cleanToken = Rd_Pdo::escapeString($token);
		$sql = "SELECT id FROM reserve_status WHERE LOWER(`token`) = LOWER({$cleanToken}) LIMIT 1;";
		$result = Rd_Pdo::one(Rd_Pdo::query($sql));
		if ($result && array_key_exists('id', $result)) {
			return $result['id'];
		} else {
			throw new Exception("Missing status token: \"{$token}\" in the database.");
		}
	}
	
	public static function lookupDisabled()
	{
		$labelField = Account_Rd::atLeastStaff() ? 'staff_label' : 'faculty_label';
		$sql = "SELECT id FROM reserve_status WHERE NOT `enabled` ORDER BY `order`, {$labelField};";
		$result = Rd_Pdo::all(Rd_Pdo::query($sql));
		return $result;
	}
	
	public static function create($staffLabel, $facultyLabel, $description, $order)
	{
		$staffLabel = Rd_Pdo::escapeString($staffLabel);
		$facultyLabel = Rd_Pdo::escapeString($facultyLabel);
		$description = Rd_Pdo::escapeString($description);
		$order = Rd_Pdo::escapeInt($order);
		$sql = 'INSERT INTO reserve_status '
			. '(staff_label, faculty_label, description, `order`) '
			. "VALUES ({$staffLabel}, {$facultyLabel}, {$description}, {$order});";
		return Rd_Pdo::insert($sql);
	}
	
	public static function update($id, $staffLabel, $facultyLabel, $description, $order, $enabled)
	{
		$id = Rd_Pdo::escapeInt($id);
		self::_assertId($id);
		$updateStaffLabel = Rd_Pdo::escapeString($staffLabel); 
		$updateFacultyLabel = Rd_Pdo::escapeString($facultyLabel);
		$updateDescription = Rd_Pdo::escapeString($description);
		$updateOrder = Rd_Pdo::escapeInt($order);
		$updateEnabled = Rd_Pdo::escapeBool($enabled);
		$setClause = self::_generateSetClause(
			array('staff_label', 'faculty_label', 'description', 'order', 'enabled'),
			array($staffLabel, $facultyLabel, $description, $order, $enabled),
			array($updateStaffLabel, $updateFacultyLabel, $updateDescription, $updateOrder, $updateEnabled)
		);
		$sql = 'UPDATE reserve_status ' . $setClause . " WHERE id = {$id};";
		$result =  Rd_Pdo::query($sql);
		if ($result) {
			return $result;
		} else {
			throw new Exception("Unable to update request status record {$id}: " . Rd_Pdo::getErrorMessage($result));
		}
	}
	
	public static function audit($reserveId, $statusId, $date, $userId)
	{
		$reserveId = Rd_Pdo::escapeInt($reserveId);
		$statusId = Rd_Pdo::escapeInt($statusId);
		$userId = Rd_Pdo::escapeInt($userId);
		$date = Rd_Pdo::escapeDate($date, TRUE);
		$sql = "INSERT INTO reserve_status_audit (reserve, reserve_status, changed_at, changed_by) VALUES ({$reserveId}, {$statusId}, {$date}, {$userId})";
		//#TODO implement debugging here
		return Rd_Pdo::insert($sql);
	}
	
	public static function clearAuditByCourseInstance($courseInstanceId)
	{
		$courseInstanceId = Rd_Pdo::escapeInt($courseInstanceId);
		$sql = "DELETE FROM reserve_status_audit WHERE `reserve` IN (SELECT reserve_id FROM reserves WHERE course_instance_id = {$courseInstanceId});";
		//#TODO implement debugging here
		return Rd_Pdo::query($sql);
	}
	
	public static function getTimeEstimateForStatus($statusId, $range = '2 weeks')
	{
		$startDate = date('Y-m-d', strtotime("-$range"));
		$endDate = date('Y-m-d', strtotime('+1 day'));
		$sql = 'SELECT '
			. '`count`, '
			. 'FLOOR(`avg` / 86400) AS days, '
			. '(FLOOR(`avg` / 3600) % 24)AS hours, '
			. '(FLOOR(`avg` / 60) % 60) AS minutes, '
			. '`avg` % 60 AS seconds, '
			. 'FLOOR(`std` / 86400) AS dev_days, '
			. '(FLOOR(`std` / 3600) % 24)AS dev_hours,'
			. '(FLOOR(`std` / 60) % 60) AS dev_minutes,'
			. '`std` % 60 AS dev_seconds '
			. 'FROM (SELECT '
			. 'COUNT(r.reserve_id) AS `count`, '
			. 'AVG(TIME_TO_SEC(TIMEDIFF(q.date_processed, a.changed_at))) AS `avg`, '
			. 'STD(TIME_TO_SEC(TIMEDIFF(q.date_processed, a.changed_at))) AS `std` '
			. 'FROM reserve_status AS s '
			. 'JOIN reserves AS r ON r.status = s.id '
			. 'JOIN requests AS q ON q.reserve_id = r.reserve_id '
			. 'JOIN reserve_status_audit AS a ON a.reserve = r.reserve_id '
			. 'WHERE r.status = 2 '
			. 'AND q.date_processed != \'0000-00-00 00:00:00\' '
			. "AND a.reserve_status = {$statusId} "
			. "AND q.date_processed >= '{$startDate}' "
			. "AND q.date_processed <= '{$endDate}' "
			. 'GROUP BY a.reserve_status) AS av';
		$result = Rd_Pdo::query($sql);
		if (!$result) {
			return array();
		} else {
			return Rd_Pdo::one($result);
		}
	}
	
	public static function getCountForStatus($statusId)
	{
		$statusId = Rd_Pdo::escapeInt($statusId);
		$sql = 'SELECT '
			. 'COUNT(s.`id`) AS `count` '
			. 'FROM reserve_status AS s '
			. 'JOIN reserves AS r ON r.`status` = s.`id` '
			. "WHERE r.`status` = {$statusId} GROUP BY s.`id`;";
		$result = Rd_Pdo::query($sql);
		if (!$result) {
			return NULL;
		} else {
			$countRow = Rd_Pdo::one($result);
			return $countRow && array_key_exists('count', $countRow) ? $countRow['count'] : 0;
		}
	}
	
}